A materialized query table (MQT) is a database table that contains a pre-computed result of a query or sub-query (a sub-query is also referred to herein as a query block). The expectation is that the same query or sub-query will be executed repeatedly. When the same query or sub-query is executed again, then a MQT substitutes the query to serve the result much faster. MQTs (also referred to as materialized views (MVs), summary tables, etc.) are widely used in database systems to improve the performance of complex queries against large databases in workloads such as decision support, data warehousing, analytics and reporting.
MQT technology improves query workload performance by pre-computing and storing the results of queries or sub-queries, and substituting the complete or partial subsequence query processing with the pre-computed results where applicable.
In adopting an MQT approach, one must undertake tasks such as defining and creating a set of MQTs (and optionally one or more indexes on those MQTs) that will likely benefit upcoming query workloads. A set of MQTs can have one or more MQTs. Another task can include substituting the queries with the most appropriate MQTs to maximize performance gains.
Defining a set of MQTs requires a high-level of database administration and performance analysis skills, as well as the understanding of the query workloads. For the users of MQTs, it is strongly desirable to have an automatic tool that can advise on what MQTs to define. There are many factors that an MQT advising tool should put into consideration to make good MQT recommendations. Those factors can include, but are not limited to, database system information, database statistics, workload characteristics and execution and frequency patterns, system constrains and user defined constrains, etc.
Existing MQT selection process approaches are missing techniques to generate MQT recommendations via a way of considering the potential maximum gain for some of the individual queries in the workload and the potential total performance gain of the entire workload. Workload information such as a schema pattern and an execution frequency pattern are among the important factors for MQT recommendation.